package com.zhan.ielts.tools;

import java.io.File;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.zhan.frame.core.utils.EncryptUtile;
import com.zhan.frame.core.utils.FileUtils;

public class CategoryWordUtils {
	private static Logger log = LoggerFactory.getLogger(CategoryWordUtils.class);
	private static Connection conn = getConnection();

	public static void main(String[] args) throws ClassNotFoundException, SQLException {
		System.out.println("---------------start");

//		String folder = "D:/xiaozhan/received files/zhu jiayi/toefl_paid_mp3_compress/";
		String folder = "D:/xiaozhan/received files/zhu jiayi/toefl_2/toefl/";
		String targetFolder = "D:/xiaozhan/received files/zhu jiayi/toefl_new/";

		categoryToefl(folder, targetFolder);

		System.out.println("---------------end");
	}

	private static void categoryToefl(String folder, String targetFolder) throws ClassNotFoundException, SQLException {
		List<String> list = getWordCategory();

		File dir = new File(folder);
		File[] files = dir.listFiles();
		List<String> filenames = Arrays.asList(dir.list());
		
//		for (File file : files) {
//			String filename = file.getName();
//			String word = filename.split("\\.")[0];
//			System.out.println(filename);
//			if (list.contains(word)) {
//				FileUtils.copyFile(file.getAbsolutePath(), targetFolder+ filename);
//			} else {
//				System.err.println("没有找到这个单词" + filename);
//			}
//
//		}
		File targetDir  = new File(targetFolder);

		List<String> errList = new ArrayList<String>();
		for (String word : list) {
			System.out.println(word);
			if (filenames.contains(word.trim()+".mp3")) {
				try {
					FileUtils.copyFile(folder+word+".mp3", targetFolder+ EncryptUtile.encrypt(word)+".mp3");
				} catch (Exception e) {
					e.printStackTrace();
				}
			} else {
//				System.err.println("没有找到这个音频" + word);
				errList.add(word);
			}

		}

		for(String errWord : errList){
			System.err.println("没有找到这个单词的音频" + errWord);
		}
	}

	public static List<String> getWordCategory() throws ClassNotFoundException, SQLException {
		PreparedStatement stmt = null;
		ResultSet rs = null;
		// Connection conn = getConnection();
		if (conn == null) {
			conn = getConnection();
		}
		conn.setAutoCommit(false);
		stmt = conn.prepareStatement("select distinct word from t_word_category order by word ");
		rs = stmt.executeQuery();
		List<String> list = new ArrayList<String>();
		while (rs.next()) {
			list.add(rs.getString(1));
		}
		return list;
	}

	public static Connection getConnection() {
		String className = "com.mysql.jdbc.Driver";
		String url = "jdbc:mysql://192.168.1.188:3306/ieltsdb?zeroDateTimeBehavior=convertToNull";
		String username = "ieltsdba";
		String userpass = "zaq1_XSW2";
		Connection con = null;
		try {
			Class.forName(className);
			con = DriverManager.getConnection(url, username, userpass);
		} catch (SQLException | ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return con;
	}

}
